﻿using pcEntity;
using pcObjectLibrary;
using System;
using System.Collections.Generic;
using System.Linq;
using Omu.ValueInjecter;
using System.Data.SqlClient;
using System.Data.Objects;
namespace pcDAL
{
    public class CompanyDAL
    {
        public int Add(CompanyObjLibrary obj)
        {
            tCompany company = new tCompany();
            using (PunchClockEntities context = new PunchClockEntities())
            {
                if (context.tUsers.Where(x => x.userName.ToLower().Equals(obj.user.userName.ToLower())).Count() > 0)
                {
                    obj.user.userId = (int)RegistrationStatusEnum.UserNameNotAvailable;
                }
                else if (context.tCompanies.Where(x => x.name.ToLower().Equals(obj.name.ToLower())).Count() > 0)
                {
                    obj.companyId = (int)RegistrationStatusEnum.DuplicateCompany;
                }
                else
                {
                    company.InjectFrom(obj);
                    company.globalId = Guid.NewGuid();
                    company.isActive = false; // Admin needs to monitor and  activate
                    company.isDeleted = false;
                    context.tCompanies.Add(company);
                    context.SaveChanges();
                }
            }
            return company.companyId;
        }

        public CompanyObjLibrary Details(int companyId)
        {
            CompanyObjLibrary company = new CompanyObjLibrary();
            using (PunchClockEntities context = new PunchClockEntities())
            {
                var _company = context.tCompanies.Where(x => x.companyId == companyId).FirstOrDefault();
                company.InjectFrom(_company);
            }
            return company;
        }

        public int Update(CompanyObjLibrary obj)
        {
            using (PunchClockEntities context = new PunchClockEntities())
            {
                var _company = context.tCompanies.Where(x => x.companyId == obj.companyId).FirstOrDefault();
                _company.name = obj.name;
                _company.summury = obj.summary;
                if (obj.logoBinary != null)
                _company.logoBinary = obj.logoBinary;
                if (obj.logoUrl != null)
                _company.logoUrl = obj.logoUrl;
                _company.deltaPunchTime = obj.deltaPunchTime;
                context.SaveChanges();
            }
            return 0;
        }

        public List<CompanyEmployeeHolidayPaidObjLibrary> GetPaidHolidayPkg(int companyId)
        {
            List<CompanyEmployeeHolidayPaidObjLibrary> pkgs = new List<CompanyEmployeeHolidayPaidObjLibrary>();
            using (PunchClockEntities context = new PunchClockEntities())
            {
                pkgs = (from et in context.tEmploymentTypes
                        join pk in context.tCompanyEmployeeHolidayPaids on et.EmploymentTypeId equals pk.employmentTypeId into pkGroup
                        from pkg in pkGroup.DefaultIfEmpty()
                        join c in context.tCompanies on pkg.companyId equals c.companyId into cGroup
                        from cg in cGroup.DefaultIfEmpty()
                        where cg.companyId == companyId || cg.companyId == null
                        select new CompanyEmployeeHolidayPaidObjLibrary
                        {
                            companyId = cg.companyId == null ? companyId : cg.companyId,
                            employmentTypeId = et.EmploymentTypeId,
                            isHolidayPaid = pkg.isHolidayPaid == null ? false : pkg.isHolidayPaid,
                            employmentTypeName = et.EmploymentTypeName
                        }).ToList();
            }
            return pkgs;
        }

        public void UpdatePaidHolidayPkg(List<CompanyEmployeeHolidayPaidObjLibrary> pkgs)
        {
            using (PunchClockEntities context = new PunchClockEntities())
            {
                var compId = pkgs.First().companyId;
                var tmpPkgs = (from pk in context.tCompanyEmployeeHolidayPaids
                               where pk.companyId == compId
                               select pk).ToList();
                string delSQL = "DELETE  TCEHP FROM    dbo.tCompanyEmployeeHolidayPaid TCEHP WHERE   TCEHP.companyId = @companyId";
                context.Database.ExecuteSqlCommand(delSQL, new SqlParameter("@companyId", compId));
                context.SaveChanges();
                foreach (var pkg in pkgs)
                {
                    string insertSQL = "INSERT INTO dbo.tCompanyEmployeeHolidayPaid ( companyId , employmentTypeId , isHolidayPaid ) VALUES  ( @companyId, @employmentTypeId, @isHolidayPaid )";
                    context.Database.ExecuteSqlCommand(insertSQL, new SqlParameter("@companyId", compId),
                        new SqlParameter("@employmentTypeId", pkg.employmentTypeId),
                        new SqlParameter("@isHolidayPaid", pkg.isHolidayPaid));
                    context.SaveChanges();
                }
            }
        }

        public List<CompanyHolidayObjLibrary>  GetCompanyHolidays(int companyId)
        {
            List<CompanyHolidayObjLibrary> retObj = new List<CompanyHolidayObjLibrary>();
            using (PunchClockEntities context = new PunchClockEntities())
            {
               retObj = (from h in context.tHolidays
                         join t in context.tHolidayAndTypes on h.holidayId equals t.holidayId
                          join ht in context.tHolidayTypes on t.typeId equals ht.holidayTypeId
                          from ch in context.tCompanyHolidays.Where(x=>x.holidayId == h.holidayId).DefaultIfEmpty()
                          where ch.companyId == companyId || ch.companyId == null
                          select new CompanyHolidayObjLibrary
                          {
                              companyId = ch.companyId,
                              holidayId = h.holidayId,
                              holidayName = h.holidayName,
                              HolidayType = ht.typeName,
                              HolidayDate = EntityFunctions.CreateDateTime(DateTime.Now.Year,h.holidayMonth,h.holidayDate, 0,0,0)
                          }).ToList();
            }
            return retObj;
        }

        public void UpdateCompanyHolidays(List<CompanyHolidayObjLibrary> hld)
        {
            using (PunchClockEntities context = new PunchClockEntities())
            {
                if (hld.Count > 0)
                {
                    hld = hld.Distinct().ToList();
                    int compId = hld.First().companyId?? 0;
                    string delSql = " DELETE TCH FROM dbo.tCompanyHoliday AS TCH WHERE TCH.companyId = @CompanyId";
                    context.Database.ExecuteSqlCommand(delSql, new SqlParameter ( "@CompanyId", compId ));
                    context.SaveChanges();
                    string insertSQL = "INSERT	INTO dbo.tCompanyHoliday ( companyId, holidayId ) VALUES  (  @CompanyId, @holidayId )";
                    foreach (var item in hld)
                    {
                        context.Database.ExecuteSqlCommand(insertSQL, new SqlParameter("@CompanyId", compId), new SqlParameter("@holidayId", item.holidayId));
                        context.SaveChanges();
                    }
                }
            }

        }
    }
}
